package DBLayer;

import java.sql.*;
import java.util.ArrayList;

import ModelLayer.*;

public class DBOrder implements IFDBOrder
{
	private Connection con;
	
	public DBOrder()
	{
		con = DbConnection.getInstance().getDBcon();
	}

	@Override
	public ArrayList<Order> getAllOrders(boolean retrieveAssociation)
	{
		return miscWhere("", retrieveAssociation);
	}

	@Override
	public Order findOrder(int id, boolean retrieveAssociation)
	{
		String wClause = "orderID = '" + id + "'";
		return singleWhere(wClause, retrieveAssociation);
	}

	@Override
	public int insertOrder(Order ord) throws Exception
	{
		int nextId = GetMax.getMaxId("Select max(id) from order");
		nextId++;
		System.out.println("next id = " + nextId);
		int rc = -1;
		String query = "INSERT INTO Orders(customerID, regDate, delDate, status, totalPrice) VALUES('"
				+ ord.getCustomer().getId() + "','"
				+ ord.getRegDate() + "','"
				+ ord.getDeliveryDate() + "','"
				+ ord.getStatus() + "','"
				+ ord.getTotalPrice() + "')";
		System.out.println("insert : " + query);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(SQLException ex)
		{
			System.out.println("Ordre ikke oprettet");
			throw new Exception("Order is not inserted correct");
		}
		return (rc);
	}

	@Override
	public int updateOrder(Order ord)
	{
		// TODO Auto-generated method stub
		
		Order ordObj = ord;
		int rc = -1;
		String query = "UPDATE product SET " + "customerID ='" + ordObj.getCustomer().getId() + "', "
				+ "regDate ='" + ordObj.getRegDate() + "', "
				+ "delDate ='" + ordObj.getDeliveryDate() + "', "
				+ "status ='" + ordObj.getStatus() + "', "
				+ "totalPrice ='" + ordObj.getTotalPrice() + "')";
		System.out.println("Update query:" + query);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			rc = stmt.executeUpdate(query);
			stmt.close();
		}
		catch(Exception ex)
		{
			System.out.println("Update exception in order db: " + ex);
		}
		return (rc);
	}
	
	private ArrayList<Order> miscWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		ArrayList<Order> list = new ArrayList<Order>();
		String query = buildQuery(wClause);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			while(results.next())
			{
				Order ordObj = new Order();
				ordObj = buildOrder(results);
				list.add(ordObj);
			}
			stmt.close();
			if(retrieveAssociation)
			{
				for(Order ordObj : list)
				{
					Customer ordCus = ordObj.getCustomer();
					ordObj.setCustomer(ordCus);
					System.out.println("Customer is selected");
				}
			}
		}
		catch(Exception e)
		{
			System.out.println("Query exception - select: " + e);
			e.printStackTrace();
		}
		return list;
	}
	
	private Order singleWhere(String wClause, boolean retrieveAssociation)
	{
		ResultSet results;
		Order ordObj = new Order();
		String query = buildQuery(wClause);
		System.out.println(query);
		
		try
		{
			Statement stmt = con.createStatement();
			stmt.setQueryTimeout(5);
			results = stmt.executeQuery(query);
			
			if(results.next())
			{
				ordObj = buildOrder(results);
				stmt.close();
				
				if(retrieveAssociation)
				{
					Customer ordCus = ordObj.getCustomer();
					ordObj.setCustomer(ordCus);
					System.out.println("Customer is selected");
				}
			}
			else
			{
				ordObj = null;
			}
		}
		catch(Exception e)
		{
			System.out.println("Query exception: " + e);
		}
		return ordObj;
	}
	
	private String buildQuery(String wClause)
	{
		String query = "SELECT customerID, regDate, delDate, status, totalPrice FROM Orders";
		
		if(wClause.length() > 0)
		{
			query = query + " WHERE " + wClause;
		}
		return query;
	}
	
	private Order buildOrder(ResultSet results)
	{
		Order ordObj = new Order();
		
		try
		{
			ordObj.setCustomer(new Customer(Integer.parseInt(results.getString("customerID"))));
			ordObj.setRegDate(Date.valueOf(results.getString("regDate")));
			ordObj.setDeliveryDate(Date.valueOf(results.getString("delDate")));
			ordObj.setStatus(results.getString("status"));
			ordObj.setTotalPrice(Double.parseDouble(results.getString("totalPrice")));
		}
		catch(Exception e)
		{
			System.out.println("error in building the order object");
		}
		return ordObj;
	}
}
